1. Field of the Invention
The present invention relates to relational database query formulation and more particularly to translation of ad-hoc query language to Structured Query Language prior to execution of a query.
2. Description of the Related Art
A relational database often includes tables which contain records that have a zero-to-many relationship to records in other tables. For example, a table of movie titles may be related to a second table which contains a list of actors, and to a third table which contains a list of writers. The actor and writer tables contain a foreign key referencing back to the titles of movies. In this way, each record in the actors or writers table represents an actor's or writer's participation in a particular movie. The same actor may appear zero to many times representing each movie he/she performed in. Likewise, the movie "key" may appear in multiple records since, naturally, many actors appear in any one movie. For simplicity, the title table may be referred to as a "parent" table and the actor and writer tables as "child" tables.
The challenge is to formulate a valid SQL query that can return items from the parent table based on an ad-hoc query from a user interface. The allowable fields for the user can be from either the parent or any of its child fields. For example, the user may choose to query for movies in which two distinct actors appeared together.
Some database query systems, like the "Find" Assistant of Lotus Approach 97 (trademarks of Lotus Development Corporation) when used with DBASE (DBF) files, choose not to allow this condition, forcing the user to enter only one name per field chosen. Pre SQL-3 compliant systems might provide the user with a means to enter the query in some ad-hoc language (e.g. actor="CHER" or actor="OLYMPIA DUKAKIS") and then actually submit two or more separate database queries to the underlying database and manage the results list in the middleware between the GUI and database. This can lead to poor performance since each subsequent query returns a full result list for the particular query clause. Other database query systems provide the user with direct access to SQL, allowing them to create more complicated looking, but effective queries, such as:
with actor1(id) as (select title.sub.-- id from title where actor="CHER"), actor2(id) as (select title.sub.-- id from title where actor="OLYMPIA DUKAKIS")
SELECT distinct titleid, title from title, actor1, actor2 where title.titleid=actor1.id and
title.titleid=actor2.id
A less verbose but flexible database query system is needed that provides a simpler ad-hoc query interface that directly maps to a single DB2 SQL query, thereby permitting complex queries in a simple user interface without the overhead of additional result list management code. Preferably, such a system places the burden of optimizing the query on the database, which is better equipped to do the job.